# -*- coding: utf-8 -*-
import sys, getopt
import xlrd
import xlwt
import os

# 该文件对从redmine导出的文件进行整理，按照
# 统一的顺序进行排列，主要项目和第二项目分行排列，同时生成本周总工时
# 该文件可用于进行排序，统计，分类汇总等
# 

DEP_INDEX   =1   #B
WEEK_INDEX  =20  #U
AUTHOR_INDEX=7   #H
P1NAME_INDEX=23  #X
P1TIME_INDEX=24  #Y
P1JOB_INDEX =25  #Z
P2NAME_INDEX=26  #AA
P2TIME_INDEX=27  #AB
P2JOB_INDEX =28  #AC

# week starts with 

from wtcommon import *

def convert_table(filename, min_week=0, max_week=55, newfilename='new.xls'):
    wb = xlrd.open_workbook(filename)

    nwb = xlwt.Workbook()

    sheet = nwb.add_sheet('Formatted')

    line = set_sheet_format(sheet)

    for s in wb.sheets():
        for row in range(1, s.nrows):
            dep    = s.cell(row, DEP_INDEX   ).value.strip()
            week   = int(s.cell(row, WEEK_INDEX  ).value[5:7])
            name   = s.cell(row, AUTHOR_INDEX).value.strip().replace(' ', '')
            p1name = s.cell(row, P1NAME_INDEX).value.strip()
            p1job  = s.cell(row, P1JOB_INDEX ).value
            p1time = s.cell(row, P1TIME_INDEX).value
            p2name = s.cell(row, P2NAME_INDEX).value.strip()
            p2job  = s.cell(row, P2JOB_INDEX ).value
            p2time = s.cell(row, P2TIME_INDEX).value
            #print dep, week, name, p1name, p1time, p2name, p2time
            if isinstance(p2time, str):
                ttime = p1time
            else:
                ttime = p1time + p2time

            if (week >= min_week and week <= max_week):
                line = write_line(sheet, line, [dep, week, name, p1name, p1time, ttime, p1job])
                if p2name != "":
                    line = write_line(sheet, line, [dep, week, name, p2name, p2time, ttime, p2job])

    nwb.save(newfilename)

def usage():
    print u"""
%s [OPTS] <filename>
   --min=min_week   起始年周数
   --max=max_week   终止年周数
   -o output        输出文件，以xls结束
   -h, --help       帮助
   --version        版本
    """%os.path.split(sys.argv[0])[-1]

if __name__ == '__main__':
    opts, args = getopt.getopt(
        sys.argv[1:], "ho:", 
        [
            "help", "version", "min=", "max="
        ])
    if len(args) == 0 and len(opts) == 0:
        usage()
        sys.exit(2)

    minw = 0
    maxw = 55
    output = 'new.xls'
    for o, a in opts:
        if o in ('--min'):
            minw = int(a)
        elif o in ('--max'):
            maxw = int(a)
        elif o in ('-o'):
            output = a
        elif o in ('--version'):
            usage()
            sys.exit(2)
        else:
            pass

    convert_table(args[0], minw, maxw, output)
